package com.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.dao.PersonDao;
import com.po.Person;
import com.util.DbUtil;

public class PersonDaoImpl implements PersonDao {
	SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

	// Date date = formatter.parse(s);

	@Override
	public int addPerson(Person person) {
		String sql = "insert into person (id,pname,birthday,tel,did,salary,password) values (kinva_seq_person.nextval,?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement pst = null;
		con = DbUtil.connect();
		int m = 0;
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, person.getPname());
			if (person.getBirthday() == null) {
				pst.setDate(2, null);
			} else {
				Date date = formatter.parse(person.getBirthday());
				java.sql.Date date2 = new java.sql.Date(date.getTime());
				pst.setDate(2, date2);
			}
			pst.setString(3, person.getTel());
			pst.setInt(4, person.getDid());
			pst.setInt(5, person.getSalary());
			pst.setString(6, person.getPname());
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		} finally {
			DbUtil.free(con, pst, null);
		}

		return m;
	}

	@Override
	public int deletePerson(int id) {
		String sql = "delete from person where id=?";
		Connection con = null;
		PreparedStatement pst = null;
		con = DbUtil.connect();
		int m = 0;
		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);

			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.free(con, pst, null);
		}
		return m;
	}

	@Override
	public int updatePerson(Person person) {
		String sql = "update person set pname=?,birthday=?,tel=?,did=?,salary=?,password=? where id=?";
		Connection con = null;
		PreparedStatement pst = null;
		con = DbUtil.connect();
		int m = 0;
		try {
			pst = con.prepareStatement(sql);
			con.setAutoCommit(false);
			pst.setString(1, person.getPname());
			Date date = formatter.parse(person.getBirthday());
			java.sql.Date date2 = new java.sql.Date(date.getTime());
			pst.setDate(2, date2);
			pst.setString(3, person.getTel());
			pst.setInt(4, person.getDid());
			pst.setInt(5, person.getSalary());
			pst.setString(6, person.getPname());
			pst.setInt(7, person.getId());
			m = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtil.free(con, pst, null);
		}

		return m;
	}

	@Override
	public List<Person> showAllPerson() {
		List<Person> ps = new ArrayList<Person>();

		String sql = "select * from person";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = DbUtil.connect();
		try {
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				String cname = rs.getString("pname");
				Date date = rs.getDate("birthday");
				String birth = formatter.format(date);
				String tel = rs.getString("tel");
				int departId = rs.getInt("did");
				int salary = rs.getInt("salary");
				Person person = new Person(cname, birth, tel, departId, salary);
				person.setId(id);
				ps.add(person);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DbUtil.free(con, pst, null);
		}
		return ps;
	}

	@Override
	public String showPersonwithDepartment(int did) {
		List<Person> ps = new ArrayList<Person>();
		StringBuilder sBuilder = new StringBuilder();
		sBuilder.append("id\t pname\t birthday\t tel\t\t salary\t did\t dname\t city \n");
		String sql = "select * from (select ID,PNAME,PASSWORD,BIRTHDAY,TEL,SALARY,PERSON.DID,DNAME,CITY from Person left join DEPARTMENTS on Person.did = departments.did) where did=?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = DbUtil.connect();
		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, did);
			rs = pst.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("id");
				String cname = rs.getString("pname");
				Date date = rs.getDate("birthday");
				String birth = null;
				if (date != null) {
					birth = formatter.format(date);
				}
				String tel = rs.getString("tel");
				int departId = rs.getInt("did");
				int salary = rs.getInt("salary");
				Person person = new Person(cname, birth, tel, departId, salary);
				person.setId(id);
				ps.add(person);
				String dname = rs.getString("dname");
				String city = rs.getString("city");
				sBuilder.append(id + "\t " + cname + "\t " + birth + "\t " + tel + "\t " + salary + "\t " + did + "\t "
						+ dname + "\t " + city + "\n");
			}
			if (sBuilder.length() < 60) {
				return "There is no person in this department";
			}

		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DbUtil.free(con, pst, null);
		}
		return sBuilder.toString();
	}

	@Override
	public int deletePersonWithDid(int id) {
		String sql = "delete from person where did=?";
		Connection con = null;
		PreparedStatement pst = null;
		con = DbUtil.connect();
		int m = 0;
		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);

			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.free(con, pst, null);
		}
		return m;
	}

	@Override
	public Person loadPerson(int id) {
		Person personNew = null;

		String sql = "select * from person where id=?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = DbUtil.connect();
		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			if (rs.next()) {
				String cname = rs.getString("pname");
				Date date = rs.getDate("birthday");
				String birth = null;
				if (date != null) {
					birth = formatter.format(date);
				}
				String tel = rs.getString("tel");
				int departId = rs.getInt("did");
				int salary = rs.getInt("salary");
				personNew = new Person(cname, birth, tel, departId, salary);
				personNew.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.free(con, pst, null);
		}
		return personNew;
	}

	@Override
	public Person loadPersonWithname(String name) {
		Person personNew = null;

		String sql = "select * from person where pname=? ";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = DbUtil.connect();
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, name);
			rs = pst.executeQuery();
			if (rs.next()) {
				String cname = rs.getString("pname");
				Date date = rs.getDate("birthday");
				String birth = null;
				if (date != null) {
					birth = formatter.format(date);
				}
				String tel = rs.getString("tel");
				int departId = rs.getInt("did");
				int salary = rs.getInt("salary");
				int id = rs.getInt("id");
				personNew = new Person(cname, birth, tel, departId, salary);
				personNew.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.free(con, pst, null);
		}
		return personNew;
	}

}
